# -*- coding: utf-8 -*-
# @Author: QX
# @File: ExcelCaseParser.py
'''pip install pandas'''
import json
import os
import ast
import uuid

import pandas  as pd
import yaml

from core.globalContext import g_context


def readExcel(filename):
    df=pd.read_excel(filename)
    #print(df)
    #转成字典
    row_to_dict=df.to_dict(Series='records')
    #转成列表
    row_to_list=df.values.tolist()
    print(row_to_list)
    return(row_to_dict)

#{'_database': {'mysql001': {'host': 'shop-xo.hctestedu.com', 'port': 3306, 'user': 'api_test', 'password': 'Aa9999!', 'db': 'shopxo_hctested'}}, 'URL': 'http://shop-xo.hctestedu.com', 'USERNAME': 'hami'}

#优化读出yaml文件的函数（传路径）----读出context.yaml中的内容并存到全局的dict中
def load_context_from_excel(folder_path):
    '''

    :param folder_path: 测试用例的路径
    :return:
    '''
    try:
        excel_file_path = os.path.join(folder_path,'context.xlsx')
        df = pd.read_excel(excel_file_path)
        print(df)
        data = {'_database': {}}
        for index , row  in df.iterrows():

            if row['类型']=='变量' :
                data[row['变量描述']] = row['变量值']

            if row['类型']=='数据库':
               db_name = row['变量描述']
               db_config = json.loads(row['变量值'])
               data['_database'][db_name] =  db_config
        print("加载context.excel内容:", data)
        if data:
           g_context().set_by_dict(data)

    except  Exception  as e:
        print(f"装载excel文件失败：{str(e)}")

'''优化读出yaml文件的函数--读出指定目录下
的所有满足“数据_名称.yaml”这个条件的所有文件名，
组合成多个测试用例
'''
def load_excel_files(folder_path):
    excel_caseinfos = []
    # a.调用存储context.excel的函数
    load_context_from_excel(folder_path)
    # b.获得指定目录下的所有满足条件文件名
    file_names = []
    allfiles=os.listdir(folder_path)
    #获得指定文件夹下数字开头，并用下划线分隔，.xlsx为扩展名的所有文件夹
    for f in allfiles:
        if f.endswith('.xlsx')  and  f.split('_')[0].isdigit():
            file_number =  int(f.split('_')[0])
            file_names.append((file_number,f))
    file_names.sort()
    file_names = [f[-1]  for f in  file_names]
    #print(file_names)
    #获得extend/keywords.yaml的路径，，并读出来内容，变成列表，为后面的参数提供key
    current_dir = os.path.abspath(os.path.dirname(__file__))
    parent_dir = os.path.dirname(current_dir)
    keywords_file_path = os.path.join(parent_dir, 'extend/keywords.yaml')
    keywords_info = {}
    with open(keywords_file_path, 'r', encoding='utf-8') as f:

        keywords_info = yaml.full_load(f)


    # c.组合文件路径并读出测试用例
    for file_name in file_names:
        file_path = os.path.join(folder_path,file_name)
        data = pd.read_excel(file_path,sheet_name=0)
        #把data中的所有的nan转为None，并获取非空值
        data = data.where(data.notnull(), None)
        #把excel转为字典
        data = data.to_dict(orient="records")
        #定义放一条测试用例的变量
        current_test_case = None
        for row in data:
            #print(row)
            #处理测试用的标题
            if pd.notna(row['测试用例标题']):
                if current_test_case is not None:
                    excel_caseinfos.append(current_test_case)

                current_test_case={'desc': row['测试用例标题'],
                                   "storyName": row['一级模块'],
                                   "featureName": row['一级模块'],
                                   'steps': []}
            #处理steps
            steps = {
                row['步骤描述']: {
                    '关键字': row['关键字']
                }
            }
            #处理requests发送的参数
            param = []
            for key,value in row.items():
                if '参数_'  in key:
                    try :
                        value = ast.literal_eval(value)
                    except Exception  as e:
                        pass
                    param.append(value)
            # 对应的KEY和value进行对应--组装requests的参数
            dict_param = {k: v for k, v in zip(keywords_info[row['关键字']], param)}
            # 组装好的参数就要放到步骤steps中
            steps[row['步骤描述']].update(dict_param)
            #再把steps加到测试用例
            current_test_case['steps'].append(steps)
        #把单个用例加到总用例变量中
        if current_test_case is not None:
            excel_caseinfos.append(current_test_case)

    return excel_caseinfos

'''
统一一下格式，为后面的新增插件作为储备
获得满足条件文件夹的文件作为测试用例，多个文件
{
        "case_infos": case_infos,
        "case_names": case_names
    }
'''
def excel_case_parser(folder_path):
    #1.定义放测试用例和测试名称的变量
    case_infos = []
    case_names = []
    #2.测试用例的来源
    excel_caseinfos=load_excel_files(folder_path)
    #3.遍历所有的测试用例，然后进统一格式的处理
    for caseinfo  in excel_caseinfos:
        case_name = caseinfo.get('desc', uuid.uuid4().__str__())
        caseinfo.update({"_case_name": case_name})
        case_infos.append(caseinfo)
        case_names.append(case_name)

    return {
        "case_infos": case_infos,
        "case_names": case_names
    }


if __name__ == '__main__':
    folder_path = 'D:\Apirun2411\examples\excel_testcase'

    print(excel_case_parser(folder_path))